package org.noahsark.core.dao;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Random;

import javax.persistence.EntityManager;
// 这个地方不导入javax.persistence.Query，而是直接使用javax.persistence.Query来定义对象
/*import javax.persistence.Query;*/

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.SQLQuery;
// 这个地方不导入org.hibernate.Query，而是直接使用org.hibernate.Query来定义对象
/*import org.hibernate.Query;*/
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.hibernate.type.Type;
import org.noahsark.core.dao.PaginationConfiguration.Ordering;
import org.noahsark.core.domainmodel.BaseEntity;
import org.noahsark.core.domainmodel.IDBEnum;
import org.noahsark.core.domainmodel.YNOption;
import org.noahsark.core.enumeration.SQLType;
import org.noahsark.core.exception.NoSingleRecordException;
import org.noahsark.utilities.common.StringUtils;

/**
 * 
 * 根据输入的参数和分页条件维护内部的JPQL和HQL语句， 可以根据内部的JPQL和HQL进行find和ecount操作.
 * 
 */
public class QueryBuilder {

    private static final int PARAM_COUNT = 1000;

    /**
     * 
     * logical operator enum.
     * 
     */
    public enum LogicalOperator {
        AND, OR;
    }

    private Log log = LogFactory.getLog(QueryBuilder.class);

    private StringBuffer sqlStringBuffer;
    private Map<String, Object> paramsMap; // SQL参数map
    private SQLType sqlType; // SQL 类别
    private Class<? extends Serializable> resultClass = null;

    private PaginationConfiguration paginationConfig; //
    // 是否已经加载了条件，根据此字段添加where关键词
    private boolean hasOneOrMoreCriteria = false;
    private int clauseCount; // 括号计数
    private Map<String, Ordering> defaultOrders;
    private Map<String, Ordering> mustOrders;
    private List<String> groupBys;
    private String havingSqlStr = null;

    private List<String> scalars;
    private Map<String, Type> scalarMaps;
    private boolean hibernateSpecial = false;

    /*
     * 每个需要排序的jpql都要有个alias，其它关联对象最好不要提供alias， 都通过主对 象导航过去。 页面排序和过滤都不用知道jpql中的别名，直接将表达式传递过来， 在本类里根据
     * 表 达式获取获取类型，然后根据alias构造出排序或filter
     */
    private String alias;

    /**
     * 构造函数。SQLType默认为JPQL。参数的格式为：from 实体名 别名。如：“from Message m”。
     * 
     * @param jpql
     */
    public QueryBuilder(String jpql) {
        this(null, jpql, SQLType.JPQL);
    }

    /**
     * 
     * constructor.
     * 
     * @param paginationConfig
     * @param jpql
     */
    public QueryBuilder(PaginationConfiguration paginationConfig, String jpql) {
        this(paginationConfig, jpql, SQLType.JPQL);
    }

    /**
     * 
     * constructor.
     * 
     * @param sql
     * @param type
     */
    public QueryBuilder(String sql, SQLType type) {
        this(null, sql, type);
    }

    /**
     * 
     * 构造函数。
     * 
     * @param sql 不带条件的sql语句，只包括select和from部分，如：“from Message m”。
     * @param type 表明该sql是native SQL，HQL，还是 JPQL
     * @param paginationConfig 分页排序和过滤的配置类，不需要该功能可以将此字段置为空
     */
    public QueryBuilder(PaginationConfiguration paginationConfig, String sql, SQLType type) {
        this.sqlStringBuffer = new StringBuffer(sql);
        this.paginationConfig = paginationConfig;
        this.sqlType = type;
        this.paramsMap = new HashMap<String, Object>();
        this.clauseCount = 0;
    }

    /**
     * constructor.使用NativeSQL查询，将将查询结果直接转换成VO对象. Example： nativeSQL =
     * "select t.transcode as trnc, bc.billcateg_code as billingCategoryCode, " +
     * "bs.billclass_code as billClassCode, bs.effective_from as billClassEffectiveFrom, " +
     * "bs.effective_to as billClassEffectiveTo " + "from ..." VO中的属性，目前只支持基本属性。Date，String等，不支持枚举。
     * 
     * @param sql 不带条件的sql语句，只包括select和from部分
     * @param type 表明该sql是native SQL还是 JPQL
     * @param paginationConfig 分页排序和过滤的配置类，不需要该功能可以将此字段置为空
     * @param resultClass resultClass
     * @param scalars List<String> 查询结果集中的别名列表，与VO中的属性名相同
     */
    public QueryBuilder(PaginationConfiguration paginationConfig, String sql, SQLType type, Class<? extends Serializable> resultClass,
            List<String> scalars) {
        this.sqlStringBuffer = new StringBuffer(sql);
        this.paginationConfig = paginationConfig;
        this.sqlType = type;
        this.paramsMap = new HashMap<String, Object>();
        this.clauseCount = 0;
        this.resultClass = resultClass;
        this.scalars = scalars;
        this.hibernateSpecial = true;
    }

    /**
     * 
     * QueryBuilder.
     * 
     * @param paginationConfig 分页
     * @param sql sql语句
     * @param type nativesql
     * @param resultClass vo class
     * @param scalarMaps object 设置alias和type，解决Number认为是BigDecimal的问题
     */
    public QueryBuilder(PaginationConfiguration paginationConfig, String sql, SQLType type, Class<? extends Serializable> resultClass,
            Map<String, Type> scalarMaps) {
        this.sqlStringBuffer = new StringBuffer(sql);
        this.paginationConfig = paginationConfig;
        this.sqlType = type;
        this.paramsMap = new HashMap<String, Object>();
        this.clauseCount = 0;
        this.resultClass = resultClass;
        this.scalarMaps = scalarMaps;
        this.hibernateSpecial = true;
    }

    /**
     * constructor.
     * 
     * @param sql 不带条件的sql语句，只包括select和from部分
     * @param type 表明该sql是native SQL还是 JPQL
     * @param paginationConfig 分页排序和过滤的配置类，不需要该功能可以将此字段置为空
     * @param resultClass resultClass
     */
    public QueryBuilder(PaginationConfiguration paginationConfig, String sql, SQLType type, Class<? extends Serializable> resultClass) {
        this.sqlStringBuffer = new StringBuffer(sql);
        this.paginationConfig = paginationConfig;
        this.sqlType = type;
        this.paramsMap = new HashMap<String, Object>();
        this.clauseCount = 0;
        this.resultClass = resultClass;
    }

    /**
     * 只在自动排序、filter时使用
     * 
     * 当构造函数中的jpql语句带left join时页面自动排序、filter功能不能使用，调用该方法设置别名后就可以使用了. String jpql =
     * "select o from ScuChargeApp as o " + "left join o.seriesType left join o.transactionType";
     * QueryBuilder qb = new QueryBuilder(configuration,jpql); qb.setAlias("o");//jpql中的别名
     * 
     * @param alias alias
     */
    public void setAlias(String alias) {
        this.alias = alias;
    }

    /**
     * 增加sql条件，操作符为AND。
     * sql中可以带参数也可以不带参数，如果带参数就要紧接着调用addParam方法给每个参数赋值.
     * 
     * @param sql 不要带where，否则结果会有两个where。如：
     * “qb.addSql(" where m.title='abc'").addSql("m.id=61");”
     * 的结果为：“select count(*) from Message m where  where m.title='abc' and m.id=61”。
     * 当sql中带参数时，sql中参数以':'开始。
     * @return QueryBuilder
     */
    public QueryBuilder addSql(String sql) {
        return this.addSqlCriterion(sql, null, null, LogicalOperator.AND);
    }

    /**
     * 增加or条件，操作符为OR。其他内容可以参考addSql方法。
     * 
     * @param sql
     * @return QueryBuilder
     */
    public QueryBuilder addOrSql(String sql) {
        return this.addSqlCriterion(sql, null, null, LogicalOperator.OR);
    }

    /**
     * 与addSql(String sql)配合使用，paramName是sql中的参数名称.
     * 
     * @param paramName
     * @param value
     * @return QueryBuilder
     */
    public QueryBuilder addParam(String paramName, Object value) {
        paramsMap.put(paramName, value);
        return this;
    }

    /**
     * 按字段添加条件，字段值可以是任意对象，如果字段值是空就不会增加该条件。 字符串条件不区分大小写.
     * 
     * @param fieldName 字段名称
     * @param operator 操作符
     * @param fieldValue 字段值是Object, if 'null' ignore this Criterion.
     * @return QueryBuilder
     */
    public QueryBuilder addCriterion(String fieldName, String operator, Object fieldValue) {
        return this.addCriterion(fieldName, operator, fieldValue, false, LogicalOperator.AND);
    }

    /**
     * 增加or条件，条件内容同addCriterion.
     * 
     * @param fieldName 字段名称
     * @param operator 操作符
     * @param fieldValue 字段值是Object
     * @return QueryBuilder
     */
    public QueryBuilder addOrCriterion(String fieldName, String operator, Object fieldValue) {
        return this.addCriterion(fieldName, operator, fieldValue, false, LogicalOperator.OR);
    }

    /**
     * 按字段添加条件，字段值是字符串，如果字段值是空就不会增加该条件.
     * 
     * @param fieldName 字段名称
     * @param operator 操作符
     * @param fieldValue 字段值是字符串
     * @param caseInsensitive true：区分大小写，false：不区分大小写
     * @return QueryBuilder
     */
    public QueryBuilder addCriterion(String fieldName, String operator, String fieldValue, boolean caseInsensitive) {
        return this.addCriterion(fieldName, operator, fieldValue, caseInsensitive, LogicalOperator.AND);
    }

    /**
     * 增加or条件，条件内容同addCriterion.
     * 
     * @param fieldName 字段名称
     * @param operator 操作符
     * @param fieldValue 字段值是字符串
     * @param caseInsensitive true：区分大小写，false：不区分大小写
     * @return QueryBuilder
     */
    public QueryBuilder addOrCriterion(String fieldName, String operator, String fieldValue, boolean caseInsensitive) {
        return this.addCriterion(fieldName, operator, fieldValue, caseInsensitive, LogicalOperator.OR);
    }

    /**
     * 
     * add criterion.
     * 
     * @param fieldName
     * @param operator
     * @param fieldValue
     * @param caseInsensitive true：不区分大小写，false： 区分大小写.
     * @param andOr
     * @return
     */
    private QueryBuilder addCriterion(String fieldName, String operator, Object fieldValue, boolean caseInsensitive,
                                      LogicalOperator andOr) {
        if (StringUtils.isBlank(fieldValue)) {
            return this;
        }
        StringBuffer sql = new StringBuffer();
        String param = this.convertFieldToParam(fieldName);
        Object nvalue = fieldValue;

        if ((caseInsensitive) && (fieldValue instanceof String)) {
            sql.append("lower(" + fieldName + ")");
            nvalue = ((String) fieldValue).toLowerCase();
        } else {
            sql.append(fieldName);
        }
        // if operator is 'in'
        if (operator.trim().toLowerCase().equals("in")) {
            sql.append(" " + operator + " ( :" + param + ") ");
        } else {
            sql.append(" " + operator + " :" + param);
        }

        return this.addSqlCriterion(sql.toString(), param, nvalue, andOr);
    }

    /**
     * 添加字段的in条件.
     * 
     * @param fieldName 字段名称
     * @param list in的参数是list集合
     * @return QueryBuilder
     */
    public QueryBuilder addInCriterion(String fieldName, List<?> list) {
        return this.addInCriterion(fieldName, list, LogicalOperator.AND);
    }

    /**
     * 添加字段的in条件.
     * 
     * @param fieldName 字段名称
     * @param list in的参数是list集合
     * @return QueryBuilder
     */
    public QueryBuilder addOrInCriterion(String fieldName, List<?> list) {
        return this.addInCriterion(fieldName, list, LogicalOperator.OR);
    }

    private QueryBuilder addInCriterion(String fieldName, List<?> list, LogicalOperator andOr) {

        StringBuffer sql = new StringBuffer(fieldName);
        String param = convertFieldToParam(fieldName);
        sql.append(" in (:");
        sql.append(param);
        sql.append(")");
        // fieldName in (:param)
        return this.addSqlCriterion(sql.toString(), param, list, andOr);
    }

    /**
     * 添加字段的not in条件.
     * 
     * @param fieldName 字段名称
     * @param list in的参数是list集合
     * @return QueryBuilder
     */
    public QueryBuilder addNotInCriterion(String fieldName, List<?> list) {
        return this.addNotInCriterion(fieldName, list, LogicalOperator.AND);
    }

    /**
     * 添加字段的not in条件.
     * 
     * @param fieldName 字段名称
     * @param list in的参数是list集合
     * @return QueryBuilder
     */
    public QueryBuilder addOrNotInCriterion(String fieldName, List<?> list) {
        return this.addNotInCriterion(fieldName, list, LogicalOperator.OR);
    }

    private QueryBuilder addNotInCriterion(String fieldName, List<?> list, LogicalOperator andOr) {

        StringBuffer sql = new StringBuffer(fieldName);
        String param = convertFieldToParam(fieldName);
        sql.append(" not in (:");
        sql.append(param);
        sql.append(")");
        // fieldName in (:param)
        return this.addSqlCriterion(sql.toString(), param, list, andOr);
    }

    /**
     * add like Criterion,auto add%.
     * 
     * @param field field
     * @param value value
     * @param caseInsensitive true：区分大小写，false：不区分大小写
     * @return QueryBuilder
     */
    public QueryBuilder addLikeCriterion(String field, String value, boolean caseInsensitive) {
        if (StringUtils.isBlank(value)) {
            return this;
        }

        String v = value + "%";
        return this.addCriterion(field, "like", v, caseInsensitive, LogicalOperator.AND);
    }

    /**
     * add like Criterion,auto add%.
     * 
     * @param field field
     * @param value value
     * @param caseInsensitive 大小写
     * @return QueryBuilder
     */
    public QueryBuilder addOrLikeCriterion(String field, String value, boolean caseInsensitive) {
        if (StringUtils.isBlank(value)) {
            return this;
        }

        String v = value + "%";
        return this.addCriterion(field, "like", v, caseInsensitive, LogicalOperator.OR);
    }

    /**
     * add is null/not null Criterion, use 'AND' as LogicalOperator.
     * 
     * @param fieldName field name.
     * @param isEquals if <code>true</code> add 'is null', else add 'is not null'.
     * @return querybuilder.
     */
    public QueryBuilder addNullCriterion(String fieldName, boolean isEquals) {
        String nullSql = String.format(" %s is %s ", fieldName, isEquals ? "null" : "not null");
        return this.addSqlCriterion(nullSql, null, null, LogicalOperator.AND);
    }

    /**
     * add is null/not null Criterion, use 'OR' as LogicalOperator.
     * 
     * @param fieldName field name.
     * @param isEquals if <code>true</code> add 'is null', else add 'is not null'.
     * @return querybuilder.
     */
    public QueryBuilder addOrNullCriterion(String fieldName, boolean isEquals) {
        String nullSql = String.format(" %s is %s ", fieldName, isEquals ? "null" : "not null");
        return this.addSqlCriterion(nullSql, null, null, LogicalOperator.OR);
    }

    /**
     * 将拼好的条件语句加到sql语句上,将参数加入参数列表.
     * 
     * @param string
     * @param param
     * @param nvalue
     * @return QueryBuilder
     */
    private QueryBuilder addSqlCriterion(String sql, String param, Object value, LogicalOperator andOr) {
        if (param != null && StringUtils.isBlank(value)) {
            return this;
        }

        this.appendCriteriaPrefix(andOr);

        this.sqlStringBuffer.append(sql);

        if (param != null) {
            paramsMap.put(param, value);
        }

        hasOneOrMoreCriteria = true;
        return this;
    }

    /**
     * 查询一天内的，起始时间是从0点--24点.
     * 
     * @param field 查询字段
     * @param value 查询日期
     * @return QueryBuilder
     */
    public QueryBuilder addCriterionDateTruncatedToDay(String field, Date value) {
        return this.addCriterionDateTruncatedToDay(field, value, LogicalOperator.AND);
    }

    /**
     * 查询一天内的，起始时间是从0点--24点.
     * 
     * @param field 查询字段
     * @param value 查询日期
     * @return QueryBuilder
     */
    public QueryBuilder addOrCriterionDateTruncatedToDay(String field, Date value) {
        return this.addCriterionDateTruncatedToDay(field, value, LogicalOperator.OR);
    }

    private QueryBuilder addCriterionDateTruncatedToDay(String field, Date value, LogicalOperator andOr) {
        if (StringUtils.isBlank(value)) {
            return this;
        }
        Calendar c = Calendar.getInstance();
        c.setTime(value);
        int year = c.get(Calendar.YEAR);
        int month = c.get(Calendar.MONTH);
        int date = c.get(Calendar.DATE);
        c.set(year, month, date, 0, 0, 0);
        Date start = c.getTime();
        c.set(year, month, date, 23, 59, 59);
        Date end = c.getTime();

        String fieldPrefix = convertFieldToParam(field);
        String fieldStart = fieldPrefix + "_start";
        String fieldEnd = fieldPrefix + "_end";

        if (andOr == LogicalOperator.AND) {
            startAndClause();
        } else {
            startOrClause();
        }
        this.addSqlCriterion(field + ">=:" + fieldStart, fieldStart, start, LogicalOperator.AND);
        this.addSqlCriterion(field + "<=:" + fieldEnd, fieldEnd, end, LogicalOperator.AND);
        this.endClause();

        return this;
    }

    /**
     * 增加默认排序，就是在查询语句中添加，可以多次增加.
     * 
     * @param orderColumn orderColumn
     * @param ordering ordering
     */
    public void addDefaultOrderBy(String orderColumn, Ordering ordering) {
        if (this.defaultOrders == null) {
            defaultOrders = new LinkedHashMap<String, Ordering>();
        }
        defaultOrders.put(orderColumn, ordering);
    }

    /**
     * 增加必须排序，就是在查询语句中添加，可以多次增加(为防止排序字段相同引起显示错误).
     * 
     * @param orderColumn orderColumn
     * @param ordering ordering
     */
    public void addMustOrderby(String orderColumn, Ordering ordering) {
        if (this.mustOrders == null) {
            mustOrders = new LinkedHashMap<String, Ordering>();
        }
        mustOrders.put(orderColumn, ordering);
    }

    /**
     * 增加group by，可以多次增加.
     * 
     * @param groupByColumn orderColumn
     */
    public void addGroupBy(String groupByColumn) {
        if (this.groupBys == null) {
            groupBys = new ArrayList<String>();
        }
        groupBys.add(groupByColumn);
    }

    /**
     * add group by to sql.
     * 
     * @param groupByColumn groupByColumn
     */
    public void addGroupByToSql(String groupByColumn) {
        boolean flag = (sqlStringBuffer.indexOf("GROUP BY") != -1);
        if (!flag && (sqlStringBuffer.lastIndexOf(" ") != sqlStringBuffer.length() - 1)) {
            sqlStringBuffer.append(" ");
        }
        this.sqlStringBuffer.append(flag ? ", " : "GROUP BY ");
        this.sqlStringBuffer.append(groupByColumn);
    }

    /**
     * 
     * add order by.
     * 
     * @param string
     * @param ascendingSorting
     */
    private void addOrderBy(String orderColumn, Ordering ordering) {

        boolean flag = (sqlStringBuffer.indexOf("ORDER BY") != -1);
        if (!flag && (sqlStringBuffer.lastIndexOf(" ") != sqlStringBuffer.length() - 1)) {
            sqlStringBuffer.append(" ");
        }
        this.sqlStringBuffer.append(flag ? ", " : "ORDER BY ");
        this.sqlStringBuffer.append(orderColumn);
        if (ordering == Ordering.ASCENDING) {
            sqlStringBuffer.append(" ASC");
        } else {
            sqlStringBuffer.append(" DESC");
        }
    }

    /**
     * For RET POC's code.
     * 
     * @param orderColumn String
     * @param ascending boolean
     * @author yut
     */
    public void addOrderBy(String orderColumn, boolean ascending) {
        if (ascending) {
        	this.addOrderBy(orderColumn, Ordering.ASCENDING);
        } else {
        	this.addOrderBy(orderColumn, Ordering.DESCENDING);
        }

    }

    /**
     * 增加'and('.
     */
    public void startAndClause() {
        this.appendCriteriaPrefix(LogicalOperator.AND);
        this.sqlStringBuffer.append("(");
        this.clauseCount++;
        this.hasOneOrMoreCriteria = false;
    }

    /**
     * 增加'or('.
     */
    public void startOrClause() {
        this.appendCriteriaPrefix(LogicalOperator.OR);
        this.sqlStringBuffer.append("(");
        this.clauseCount++;
        this.hasOneOrMoreCriteria = false;
    }

    /**
     * 与startAndClause()或startOrClause()匹配，增加')',结束括号.
     */
    public void endClause() {
        if (this.clauseCount > 0) {
            this.sqlStringBuffer.append(")");
            this.clauseCount--;
        }
    }

    /**
     * 获得查询语句的总记录数.
     * 
     * @param em EntityManager
     * @return Long 总记录数
     */
    Long count(EntityManager em) {
        return this.count(em, false);
    }
    
    /**
     * 获得查询语句的总记录数.
     * 
     * @param sn
     * @return Long 总记录数
     */
    Long count(Session sn) {
    	log.info("method count in class QueryBuilder begin");
    	
    	Long result = this.count(sn, false);
    	
    	log.info("method count in class QueryBuilder end");
        return result;
    }

    private Long count(EntityManager em, boolean cacheable) {
    	javax.persistence.Query query = this.getQuery(em, true);

        if (cacheable) {
            query.setHint("org.hibernate.cacheable", Boolean.valueOf(true));
        }
        return ((Number) query.getSingleResult()).longValue();
    }
    
    /**
     * 
     * @param sn
     * @param cacheable
     * @return
     */
    private Long count(Session sn, boolean cacheable) {
    	log.info("method count in class QueryBuilder begin");
    	
        org.hibernate.Query query = this.getQuery(sn, true);

        if (cacheable) {
        	//暂时没有找到hibernate中能替换这行代码的
            //query.setHint("org.hibernate.cacheable", Boolean.valueOf(true));
        }
        
        Long result = ((Number) query.uniqueResult()).longValue();
        log.info("method count in class QueryBuilder end");
        return result;
    }

    /**
     * 获得查询语句的结果.
     * 
     * @param em EntityManager
     * @return List 查询语句结果
     */
    @SuppressWarnings("rawtypes")
    List find(EntityManager em) {
        return this.find(em, false);
    }
    
    /**
     * 获得查询语句的结果.
     * @param sn Session
     * @return List<?> 查询语句结果
     */
    List<?> find(Session sn){
    	return (List<?>)this.find(sn, false);
    }

    @SuppressWarnings("rawtypes")
    private List find(EntityManager em, boolean cacheable) {

        if (this.hibernateSpecial) {
            return this.queryNativeSQLToVO(em);
        }

        javax.persistence.Query query = this.getQuery(em, false);
        if (cacheable) {
            query.setHint("org.hibernate.cacheable", Boolean.valueOf(true));
        }

        return query.getResultList();
    }
    
    @SuppressWarnings("rawtypes")
    private List find(Session sn, boolean cacheable) {

        if (this.hibernateSpecial) {
            return this.queryNativeSQLToVO(sn);
        }

        org.hibernate.Query query = this.getQuery(sn, false);
        if (cacheable) {
        	//暂时没有找到hibernate中能替换这行代码的
            //query.setHint("org.hibernate.cacheable", Boolean.valueOf(true));
        }

        return query.list();
    }

    private List queryNativeSQLToVO(EntityManager em) {

        String sql = this.calcFinalSql(false);

        org.hibernate.SQLQuery query = ((Session) (em.getDelegate())).createSQLQuery(sql);

        if (scalars != null) {

            for (String str : scalars) {
                query.addScalar(str);
            }
        }

        if (scalarMaps != null) {

            for (Entry<String, Type> entry : scalarMaps.entrySet()) {

                if (entry.getValue() != null) {
                    query.addScalar(entry.getKey(), entry.getValue());
                } else {
                    query.addScalar(entry.getKey());
                }
            }
        }

        for (Map.Entry<String, Object> e : paramsMap.entrySet()) {
            // 设置查询参数
            try {
                query.setParameter(e.getKey(), e.getValue());
            } catch (Exception ex) {
                log.warn("parameter is not exist in QUERY");
            }
        }

        query.setResultTransformer(Transformers.aliasToBean(this.resultClass));

        this.applyPagination(query);
        return query.list();
    }
    
    private List queryNativeSQLToVO(Session sn) {
//
//        String sql = this.calcFinalSql(false);
//
//        org.hibernate.SQLQuery query = ((Session) (sn.getDelegate())).createSQLQuery(sql);
//
//        if (scalars != null) {
//
//            for (String str : scalars) {
//                query.addScalar(str);
//            }
//        }
//
//        if (scalarMaps != null) {
//
//            for (Entry<String, Type> entry : scalarMaps.entrySet()) {
//
//                if (entry.getValue() != null) {
//                    query.addScalar(entry.getKey(), entry.getValue());
//                } else {
//                    query.addScalar(entry.getKey());
//                }
//            }
//        }
//
//        for (Map.Entry<String, Object> e : paramsMap.entrySet()) {
//            // 设置查询参数
//            try {
//                query.setParameter(e.getKey(), e.getValue());
//            } catch (Exception ex) {
//                log.warn("parameter is not exist in QUERY");
//            }
//
//        }
//
//        query.setResultTransformer(Transformers.aliasToBean(this.resultClass));
//
//        this.applyPagination(query);
//        return query.list();
    	return null;
    }

    /**
     * applyPagination.
     * 
     * @param query object.
     */
    private void applyPagination(SQLQuery query) {
        if (paginationConfig == null) {
            return;
        }

        if (paginationConfig.getFirstRow() > 0) {
            query.setFirstResult(paginationConfig.getFirstRow());
        }
        if (paginationConfig.getNumberOfRows() > 0) {
            query.setMaxResults(paginationConfig.getNumberOfRows());
        }
    }

    /**
     * 查询语句最多只会返回一个结果，没有结果返回null，多于1个抛出异常.
     * 
     * @param em EntityManager
     * @return Object 返回一个对象或者null
     * @exception RuntimeException 如果结果超过1个就抛出异常
     */
    @SuppressWarnings("rawtypes")
    Object findSingle(EntityManager em) {
        List list = find(em);
        if (list.size() == 1) {
            return list.get(0);
        }
        // DEBUG 用
        if (list.size() > 1) {
            for (Object obj : list) {
                System.out.println("((BaseEntity)obj).getId() =:" + ((BaseEntity) obj).getId().toString());
            }
        }
        if (list.size() > 1) {
            throw new NoSingleRecordException(list.get(0).toString() + " only have one");
        }

        return null;
    }
    
    /**
     * 查询语句最多只会返回一个结果，没有结果返回null，多于1个抛出异常.
     * 
     * @param sn Session
     * @return Object 返回一个对象或者null
     * @exception RuntimeException 如果结果超过1个就抛出异常
     */
    @SuppressWarnings("rawtypes")
    Object findSingle(Session sn) {
        List list = find(sn);
        if (list.size() == 1) {
            return list.get(0);
        }
        // DEBUG 用
        if (list.size() > 1) {
            for (Object obj : list) {
                System.out.println("((BaseEntity)obj).getId() =:" + ((BaseEntity) obj).getId().toString());
            }
        }
        if (list.size() > 1) {
            throw new NoSingleRecordException(list.get(0).toString() + " only have one");
        }

        return null;
    }

    /**
     * 添加" and "，" or "，或者" where "。不符合逻辑时，直接返回。
     * @param andOr 表示添加" and "，还是" or "
     */
    private void appendCriteriaPrefix(LogicalOperator andOr) {
    	// 如果没有添加条件，且括号数量大于零，则不符合逻辑，返回。
        if (!hasOneOrMoreCriteria && this.clauseCount > 0) {
            return;
        }

        // 如果已加载条件，则选择添加" and "或" or "，否则在没有添加条件的情况下，则添加" where "
        if (hasOneOrMoreCriteria) {
            this.sqlStringBuffer.append(andOr == LogicalOperator.AND ? " and " : " or ");
        } else {
            this.sqlStringBuffer.append(" where ");
        }
    }

    /**
     * 查询参数：可以主动传入参数名称，也可以由字段名称获得参数名称，添加条件时只转入 field就使用该方法
     * 来获得参数名称.
     * @param field
     * @return
     */
    private String convertFieldToParam(String field) {
        field = field.replace(".", "_").replace("(", "_").replace(")", "_").replace(",", "_").replace(" ", "");
        StringBuilder newField = new StringBuilder(field);
        while (paramsMap.containsKey(newField.toString())) {
            newField = new StringBuilder(field).append("_" + String.valueOf(new Random().nextInt(PARAM_COUNT)));
        }
        return newField.toString();
    }

    private javax.persistence.Query getQuery(EntityManager em, boolean isCount) {

        String sql = calcFinalSql(isCount);
        javax.persistence.Query result = createQuery(em, sql, isCount);
        if (!isCount) {
            this.applyPagination(result); // 增加分页
        }

        for (Map.Entry<String, Object> e : paramsMap.entrySet()) {
            // 设置查询参数
            try {
                result.setParameter(e.getKey(), e.getValue());
            } catch (Exception ex) {
                log.warn("parameter is not exist in QUERY");
            }

        }

        return result;
    }
    
    /**
     * 
     * @param sn
     * @param isCount
     * @return
     */
    private org.hibernate.Query getQuery(Session sn, boolean isCount) {

        String sql = calcFinalSql(isCount);
        org.hibernate.Query result = this.createQuery(sn, sql, isCount);
        if (!isCount) {
            this.applyPagination(result); // 增加分页
        }

        for (Map.Entry<String, Object> e : paramsMap.entrySet()) {
            // 设置查询参数
            try {
                result.setParameter(e.getKey(), e.getValue());
            } catch (Exception ex) {
                log.warn("parameter is not exist in QUERY");
            }
        }

        return result;
    }

    /**
     * 查询前计算出sql.
     * 
     * @param isCount
     * @return
     */
    String calcFinalSql(boolean isCount) {
        // 自动加过滤器,后台不会执行
        addTableFilter();

        String sql = null;
        if (isCount) {
            sql = "select count(*) "
                    + sqlStringBuffer.toString().substring(indexof(sqlStringBuffer.toString(), "from"));

            if (sql.indexOf("ORDER BY") != -1) {
                sql = sql.substring(0, sql.indexOf("ORDER BY"));
            }

        } else { // 如果不是count，还需要加上排序和分页
            applyGroupBy();
            applyOrderBy();
            sql = this.sqlStringBuffer.toString();
        }

        if (log.isDebugEnabled()) {
            log.debug("[" + this.sqlType + "] : " + sql);
        }
        if (this.clauseCount != 0) {
            throw new RuntimeException("Have clause not closed !");
        }
        return sql;
    }

    /**
     * applyHaving. object.
     */
    private void applyHaving() {
        if (!StringUtils.isBlank(havingSqlStr)) {
            sqlStringBuffer.append(" having " + havingSqlStr + " ");
        }
    }

    public void addHavingSql(String havingSql) {
        this.havingSqlStr = havingSql;
    }

    /**
     * 页面table 自动添加filter查询条件.
     */
    private void addTableFilter() {
        if (this.paginationConfig == null || !this.paginationConfig.isFiltered()) {
            return;
        }
        for (Entry<String, FilterValue> entry : paginationConfig.getFilters().entrySet()) {
            if (entry.getValue() == null) {
                continue;
            }
            String filterName = entry.getKey();
            filterName = addAlia(filterName);
            FilterValue filterValue = entry.getValue();
            if (filterValue.getType().equals(FilterValue.TYPE_STRING)) {
                // case insensitive. field toLowcase
                String value = filterValue.getValue() == null ? null : filterValue.getValue().toString().toLowerCase();
                addLikeCriterion(filterName, value, true);
            } else if (filterValue.getType().equals(FilterValue.TYPE_NUMBER)) {
                String value = filterValue.getValue() == null ? null : filterValue.getValue().toString();
                addLikeCriterion(filterName, value, false);
            } else if (filterValue.getType().equals(FilterValue.TYPE_ENMU)) {
                /*
                 * 1)没有输入是不会触发filter 2）输入存在的枚举，执行list 3）不存在的枚举 is null.
                 */
                if (filterValue.getValue() != null) {
                    if (((List) (filterValue.getValue())).isEmpty()) {
                        addSql(filterName + " is null");
                    } else {
                        List filterValues = (List) filterValue.getValue();
                        if (!filterValues.isEmpty()) {
                            Object obj = filterValues.get(0);
                            if (obj instanceof IDBEnum) {
                                List<String> strFilterValues = new ArrayList<String>();
                                for (Object e : filterValues) {
                                    strFilterValues.add(((IDBEnum) e).getValue());
                                }
                                addInCriterion(filterName, strFilterValues);
                            } else {
                                addInCriterion(filterName, (List) filterValue.getValue());
                            }
                        }
                    }
                }
            } else if (FilterValue.TYPE_DATE.equals(filterValue.getType())) {
                String column = filterValue.getTableColumnName();

                if (!StringUtils.isBlank(column)) {

                    this.addSql(" to_char( " + column + " , 'dd-Mon-YYYY') like '%" + filterValue.getValue() + "%'");

                }
            }
        }
    }

    private int indexof(String sql, String from) {
        return sql.toLowerCase().indexOf(from.toLowerCase());
    }

    /**
     * 只在自动排序、filter时使用.
     */
    private String addAlia(String filedName) {
        if (this.alias == null) {
            return filedName;
        } else {
            return alias + "." + filedName;
        }
    }

    private javax.persistence.Query createQuery(EntityManager em, String s, boolean isCount) {
        if (this.sqlType == SQLType.JPQL) {
            return em.createQuery(s);
        } else {
            if (this.resultClass != null && !isCount) {
                return em.createNativeQuery(s, this.resultClass);
            } else {
                return em.createNativeQuery(s);
            }
        }
    }
    
    /**
     * 
     * @param sn
     * @param s
     * @param isCount
     * @return
     */
    private org.hibernate.Query createQuery(Session sn, String s, boolean isCount) {
        if (this.sqlType == SQLType.HQL) {
            return sn.createQuery(s);
        } else {
            if (this.resultClass != null && !isCount) {
                return sn.createSQLQuery(s);
            } else {
                return sn.createSQLQuery(s);
            }
        }
    }

    /**
     * 增加自动排序或默认排序，2选1，自动排序优先.
     */
    private void applyOrderBy() {
        // 自动排序，自动排序只能有一个
        if (paginationConfig != null && paginationConfig.isSorted()) {
            String sortField = addAlia(paginationConfig.getSortField());
            addOrderBy(sortField, paginationConfig.getOrdering());
        }

        // 默认排序，默认排序可以有多个
        if (defaultOrders != null) {
            for (Entry<String, Ordering> entry : defaultOrders.entrySet()) {
                addOrderBy(entry.getKey(), entry.getValue());
            }
        }

        if (mustOrders != null) {
            for (Entry<String, Ordering> entry : mustOrders.entrySet()) {
                addOrderBy(entry.getKey(), entry.getValue());
            }
        }
    }

    /**
     * 增加groupby.
     */
    private void applyGroupBy() {
        // 默认排序，默认排序可以有多个
        if (groupBys != null) {
            for (String column : groupBys) {
                addGroupByToSql(column);
            }

            applyHaving();
        }
    }

    private void applyPagination(javax.persistence.Query query) {
        if (paginationConfig == null) {
            return;
        }

        if (paginationConfig.getFirstRow() > 0) {
            query.setFirstResult(paginationConfig.getFirstRow());
        }
        if (paginationConfig.getNumberOfRows() > 0) {
            query.setMaxResults(paginationConfig.getNumberOfRows());
        }
    }
    
    private void applyPagination(org.hibernate.Query query) {
        if (paginationConfig == null) {
            return;
        }

        if (paginationConfig.getFirstRow() > 0) {
            query.setFirstResult(paginationConfig.getFirstRow());
        }
        if (paginationConfig.getNumberOfRows() > 0) {
            query.setMaxResults(paginationConfig.getNumberOfRows());
        }
    }

    /**
     * only for test.
     * 
     * @return
     */
    Map<String, Object> getParamsMap() {
        return paramsMap;
    }

    public static void main(String[] args) {
        QueryBuilder qb = new QueryBuilder("select * from AirAgtNrccAuth o", SQLType.NATIVE_SQL);
        qb.addCriterion("o.authorised", "=", YNOption.Y);
        qb.startAndClause();
        qb.startAndClause();
        qb.addCriterion("o.effectiveFrom", ">=", "11");
        qb.addCriterion("o.effectiveFrom", "<=", "22");
        qb.endClause();

        qb.startOrClause();
        qb.addNullCriterion("o.effectiveTo", true);
        qb.startOrClause();
        qb.addCriterion("o.effectiveTo", ">=", "33");
        qb.addCriterion("o.effectiveTo", "<=", "44");
        qb.endClause();
        qb.endClause();
        qb.endClause();
        qb.addGroupBy("asdf");
        qb.addHavingSql("sum(asdf) >0 ");

        qb.calcFinalSql(false);
        System.out.println(qb.sqlStringBuffer.toString());
        /* System.out.println(sqb.getQuery(null,false).toString()); */

    }

    /**
     * batch execute update/delete by JPQL or Native sql in QueryBuilder.
     * 
     * @param em - JPA EntityManager
     * @return the number of entities updated or deleted
     */
    int batchUpdate(EntityManager em) {
        javax.persistence.Query query = getQuery(em, false);
        return query.executeUpdate();
    }
    
    /**
     * batch execute update/delete by JPQL or Native sql in QueryBuilder.
     * 
     * @param sn - Hibernate Session
     * @return the number of entities updated or deleted
     */
    int batchUpdate(Session sn) {
    	org.hibernate.Query query = this.getQuery(sn, false);
        return query.executeUpdate();
    }

    public boolean isHasOneOrMoreCriteria() {
        return hasOneOrMoreCriteria;
    }

    public void setHasOneOrMoreCriteria(boolean hasOneOrMoreCriteria) {
        this.hasOneOrMoreCriteria = hasOneOrMoreCriteria;
    }

//    @Override
//    public String toString() {
//        return calcFinalSql(false);
//    }
    
    

}
